package cn.t.persistence.mapper.device;

import cn.t.persistence.entity.device.DevicePO;
import cn.t.request.InventoryReq;
import cn.t.vo.device.InventoryCargowayVO;
import cn.t.vo.device.InventoryDeviceVO;
import cn.t.vo.device.InventorySkuVO;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * 设备表(Device)领域对象与实体对象转换
 *
 * @author t
 * @since 2024-05-24 10:18:08
 */

public interface DeviceMapper extends BaseMapper<DevicePO> {

    String INVENTORY_WHERE = "<if test='param.deviceName != null'> AND d.device_name LIKE CONCAT('%', #{param.deviceName}, '%') </if>" +
            "<if test='param.deviceId != null and param.deviceId != \"\"'> AND cs.device_id = #{param.deviceId} </if>" +
            "<if test='param.firmId != null and param.firmId != \"\"' > AND d.firm_id = #{param.firmId} </if>" +
            "<if test='param.skuCode != null and param.skuCode != \"\"'> AND cs.sku_code = #{param.skuCode} </if>" +
            "<if test='param.skuName != null and param.skuName != \"\"' > AND cs.sku_name LIKE CONCAT('%', #{param.skuName}, '%') </if>";

    @Select("<script>" +
            "SELECT cs.device_id, d.device_name, d.firm_id, cs.sku_code, cs.sku_name, cs.sku_img, " +
            "SUM(cs.inventory) as inventoryQty, SUM(cs.weight) as weight, s.error_rate,s.weight as sku_weight FROM t_cargoway_sku cs " +
            "LEFT JOIN t_device d ON cs.device_id = d.id " +
            "LEFT JOIN t_sku s ON cs.sku_code = s.sku_code " +
            "WHERE cs.is_deleted = 'N' " + INVENTORY_WHERE +
            " GROUP BY cs.device_id, cs.sku_code  order by d.firm_id desc, cs.device_id DESC limit #{pageNo}, #{pageSize}" +
            "</script>")
    List<InventoryDeviceVO> findInventoryDevicePage(Integer pageNo, Integer pageSize, @Param("param") InventoryReq param);

    @Select("<script>" +
            "SELECT count( total ) FROM " +
            "(SELECT COUNT(cs.sku_code) as total FROM t_cargoway_sku cs " +
            "LEFT JOIN t_device d ON cs.device_id = d.id " +
            "LEFT JOIN t_sku s ON cs.sku_code = s.sku_code " +
            "WHERE cs.is_deleted = 'N' " + INVENTORY_WHERE +
            " GROUP BY cs.device_id, cs.sku_code) as a" +
            "</script>")
    Long getInventoryDeviceCount(@Param("param") InventoryReq param);

    @Select("<script>" +
            "SELECT d.firm_id, cs.sku_code, cs.sku_name, cs.sku_img, " +
            "SUM(cs.inventory) as inventoryQty, SUM(cs.weight) as weight, s.error_rate,s.weight as sku_weight FROM t_cargoway_sku cs " +
            "LEFT JOIN t_device d ON cs.device_id = d.id " +
            "LEFT JOIN t_sku s ON cs.sku_code = s.sku_code " +
            "WHERE cs.is_deleted = 'N' " + INVENTORY_WHERE +
            " GROUP BY cs.sku_code  order by d.firm_id desc, cs.sku_code DESC limit #{pageNo}, #{pageSize}" +
            "</script>")
    List<InventorySkuVO> findInventorySkuPage(Integer pageNo, Integer pageSize, @Param("param") InventoryReq param);

    @Select("<script>" +
            "SELECT count( total ) FROM " +
            "(SELECT COUNT(cs.sku_code) as total FROM t_cargoway_sku cs " +
            "LEFT JOIN t_device d ON cs.device_id = d.id " +
            "LEFT JOIN t_sku s ON cs.sku_code = s.sku_code " +
            "WHERE cs.is_deleted = 'N' " + INVENTORY_WHERE +
            " GROUP BY cs.sku_code) as a" +
            "</script>")
    Long getInventorySkuCount(@Param("param") InventoryReq param);

    @Select("<script>" +
            "SELECT cs.device_id, d.device_name, d.firm_id, cs.sku_code, cs.sku_name, cs.sku_img, " +
            "cs.inventory as inventoryQty, cs.weight as weight, s.error_rate,s.weight as sku_weight, " +
            "c.cargoway_no, c.cargoway_vol, cs.alarm_limit FROM t_cargoway_sku cs " +
            "LEFT JOIN t_cargoway c on cs.cargoway_id = c.id " +
            "LEFT JOIN t_device d ON cs.device_id = d.id " +
            "LEFT JOIN t_sku s ON cs.sku_code = s.sku_code " +
            "WHERE cs.is_deleted = 'N' " + INVENTORY_WHERE +
            " order by d.firm_id desc,cs.device_id DESC, cs.sku_code DESC limit #{pageNo}, #{pageSize}" +
            "</script>")
    List<InventoryCargowayVO> findCargowayPage(Integer pageNo, Integer pageSize, @Param("param") InventoryReq param);

    @Select("<script>" +
            "SELECT count(cs.sku_code) as total FROM t_cargoway_sku cs " +
            "LEFT JOIN t_cargoway c on cs.cargoway_id = c.id " +
            "LEFT JOIN t_device d ON cs.device_id = d.id " +
            "LEFT JOIN t_sku s ON cs.sku_code = s.sku_code " +
            "WHERE cs.is_deleted = 'N' " + INVENTORY_WHERE +
            "</script>")
    Long getInventoryCargowayCount(@Param("param") InventoryReq param);
}
